package com.beyondsoft.mcms.common;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.util.CellRangeAddress;

import com.beyondsoft.mcms.common.ReturnInfo;
import com.beyondsoft.mcms.common.ValidateUtil;

public class ExcelImportServiceImplFor2003 implements ExcelImportService {

	public String getMergedregionValue(HSSFSheet sheet, int rowIndex, int columnIndex) {

		int sheetMergecount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergecount; i++) {
			CellRangeAddress cellRangeAddress = sheet.getMergedRegion(i);
			int firstColumn = cellRangeAddress.getFirstColumn();
			int lastColumn = cellRangeAddress.getLastColumn();
			int firstRow = cellRangeAddress.getFirstRow();
			int lastRow = cellRangeAddress.getLastRow();
			if (rowIndex >= firstRow && rowIndex <= lastRow) {
				if (columnIndex >= firstColumn && columnIndex <= lastColumn) {
					HSSFRow row = sheet.getRow(firstRow);
					HSSFCell cell = row.getCell(firstColumn);
					if (cell.getCellType() == Cell.CELL_TYPE_BLANK) {
						return "";
					}
					return getCellValue(cell);
				}
			}
		}
		return "";
	}
	
	public String getCellValue(HSSFCell hssfCell) {
		if (hssfCell == null) {
			return null;
		}
		String strValue = "";
		BigDecimal b = null;
		DecimalFormat df = null;
		
		switch (hssfCell.getCellType()) {
		
		case Cell.CELL_TYPE_BLANK:
			strValue = getMergedregionValue(hssfCell.getSheet(), hssfCell.getRowIndex(), hssfCell.getColumnIndex());
			break;
		
		case Cell.CELL_TYPE_NUMERIC:
			if (HSSFDateUtil.isCellDateFormatted(hssfCell)) {
				SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
				strValue = dateFormat.format(hssfCell.getDateCellValue());
			} else {
				strValue = String.valueOf(hssfCell.getNumericCellValue());
				b = new BigDecimal(strValue);
				df = new DecimalFormat("#.####");
				if (ValidateUtil.isScientific(strValue)) {
					strValue = df.format(b);
				} else {
					if (b.scale() > 0) {
						strValue = df.format(b);
					} else {
						df = new DecimalFormat("#");
						strValue = df.format(b);
					}
				}
			}
			break;


		case Cell.CELL_TYPE_BOOLEAN:
			strValue = String.valueOf(hssfCell.getBooleanCellValue());
			break;
		case Cell.CELL_TYPE_ERROR:
			strValue = String.valueOf(hssfCell.getErrorCellValue());
			break;
		case Cell.CELL_TYPE_STRING:
			strValue = hssfCell.getStringCellValue();
			break;
		case Cell.CELL_TYPE_FORMULA:
			HSSFFormulaEvaluator eval = new HSSFFormulaEvaluator(hssfCell.getSheet().getWorkbook());
			CellValue cellValue = eval.evaluate(hssfCell);
			strValue = String.valueOf(cellValue.getNumberValue());
			break;
		default:
			strValue = "";
			break;
		}
		return strValue;
	}

	public ReturnInfo<List<Map<String, Object>>> importExcel(String strExcelFilePath) {
		return importExcel(strExcelFilePath, true);
	}

	public ReturnInfo<List<Map<String, Object>>> importExcel(String strExcelFilePath, boolean isIdentity) {
		File file = new File(strExcelFilePath);
		ReturnInfo<List<Map<String, Object>>> returnInfo = null;
		try {
			return importExcel(new FileInputStream(file), null, isIdentity);
		} catch (Exception ex) {
			returnInfo = new ReturnInfo<List<Map<String, Object>>>();
			returnInfo.setSuccess(false);
			returnInfo.setMessage(ex.getMessage());
			return returnInfo;
		}
	}

	public ReturnInfo<List<Map<String, Object>>> importExcel(String strExcelFilePath, Map<String, String> mapProperty) {
		File file = new File(strExcelFilePath);
		ReturnInfo<List<Map<String, Object>>> returnInfo = null;
		try {
			return importExcel(new FileInputStream(file), mapProperty, false);
		} catch (Exception ex) {
			returnInfo = new ReturnInfo<List<Map<String, Object>>>();
			returnInfo.setSuccess(false);
			returnInfo.setMessage(ex.getMessage());
			return returnInfo;
		}
	}

	public ReturnInfo<List<Map<String, Object>>> importExcel(InputStream stream) {
		return importExcel(stream, null, true);
	}

	public ReturnInfo<List<Map<String, Object>>> importExcel(InputStream stream, boolean isIdentity) {
		return importExcel(stream, null, isIdentity);
	}

	public ReturnInfo<List<Map<String, Object>>> importExcel(InputStream stream, Map<String, String> mapProperty) {
		return importExcel(stream, mapProperty, false);
	}

	private ReturnInfo<List<Map<String, Object>>> importExcel(
			InputStream stream, Map<String, String> mapProperty, boolean isIdentity) {
		
		ReturnInfo<List<Map<String, Object>>> returnInfo = new ReturnInfo<List<Map<String, Object>>>();
		
		if (stream == null) {
			returnInfo.setSuccess(false);
			returnInfo.setMessage("EXCEL文件不存在!");
			return returnInfo;
		}
		
		try {
			HSSFWorkbook hssfworkbook = new HSSFWorkbook(stream);
			HSSFSheet hssfsheet = hssfworkbook.getSheetAt(0);
			if (hssfsheet == null) {
				returnInfo.setSuccess(false);
				returnInfo.setMessage("EXCEL初始化失败!");
				return returnInfo;
			}
			if (hssfsheet.getPhysicalNumberOfRows() == 0) {
				returnInfo.setSuccess(false);
				returnInfo.setMessage("EXCEL中没有数据!");
				return returnInfo;
			}
			
			HSSFRow hssfrow = null;
			List listMap = new ArrayList();
			Map<String, Object> map = null;
			Map<String, String> mapKey = new HashMap<String, String>();
			
			int indexRow = hssfsheet.getFirstRowNum();
			hssfrow = hssfsheet.getRow(indexRow);
			if (hssfrow == null) {
				returnInfo.setSuccess(false);
				returnInfo.setMessage("EXCEL中没有数据!");
				return returnInfo;
			}
			
			int s = 0;
			for (int j = hssfrow.getFirstCellNum(); j <= hssfrow.getLastCellNum(); j++) {
				if (hssfrow.getCell(j) != null) {
					String strCellValue = "";
					if (isIdentity) {
						strCellValue = String.valueOf(s);
					} else {
						
						if (mapProperty != null && mapProperty.containsKey(strCellValue)) {
							strCellValue = mapProperty.get(strCellValue);
						} else {
							strCellValue = hssfrow.getCell(j).getStringCellValue();
						}
					}
					mapKey.put(String.valueOf(j), strCellValue);
				}
				s += 1;
			}

			for (int i = indexRow + 1; i <= hssfsheet.getLastRowNum(); i++) {
				hssfrow = hssfsheet.getRow(i);
				map = new HashMap<String, Object>();
				for (int j = hssfrow.getFirstCellNum(); j <= hssfrow.getLastCellNum(); j++) {
					
					HSSFCell hssfCell = hssfrow.getCell(j);
					if (hssfCell != null) {
						map.put(mapKey.get(String.valueOf(j)).toString(), getCellValue(hssfCell));
					}
				}
				listMap.add(map);
			}
			returnInfo.setSuccess(true);
			returnInfo.setReturnObj(listMap);
			return returnInfo;

		} catch (Exception ex) {
			returnInfo.setSuccess(false);
			returnInfo.setMessage("导入异常,错误信息为:" + ex.getMessage());
			return returnInfo;
		} finally {
			try {
				stream.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
			stream = null;
		}

	}

}
